package MEET.src.code;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MeetingDao {
    private static final String URL = "jdbc:mysql://sh-cdb-l52i6pgu.sql.tencentcdb.com:29750/schoolmeeting?" +
            "useSSL=false&" +
            "serverTimezone=Asia/Shanghai&" +
            "autoReconnect=true";
    private static final String USER = "root";
    private static final String PASSWORD = "745698Mjt123";

    public static String getDbUrl() {
        return URL;
    }

    public static String getDbUser() {
        return USER;
    }

    public static String getDbPassword() {
        return PASSWORD;
    }

    public boolean testConnection() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            return true;
        } catch (SQLException e) {
            System.err.println("数据库连接测试失败:");
            e.printStackTrace();
            return false;
        }
    }

    // 检查CID是否存在的方法
    public boolean checkConferenceExists(String cid) {
        String sql = "SELECT COUNT(*) FROM conference WHERE CID = ?";

        try (Connection conn = UserDao.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, cid);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 检查用户是否存在的方法
    public boolean checkUserExists(String userId) {
        String sql = "SELECT COUNT(*) FROM user WHERE ID = ?";

        try (Connection conn = UserDao.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, userId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean checkMeetingIdExists(String meetingId) {
        String sql = "SELECT COUNT(*) FROM meeting WHERE MID = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, meetingId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 辅助方法：检查表是否存在
    private boolean tableExists(Connection conn, String tableName) throws SQLException {
        DatabaseMetaData meta = conn.getMetaData();
        try (ResultSet rs = meta.getTables(null, null, tableName, null)) {
            return rs.next();
        }
    }

    // 辅助方法：检查列是否存在
    private boolean columnExists(Connection conn, String tableName, String columnName) throws SQLException {
        DatabaseMetaData meta = conn.getMetaData();
        try (ResultSet rs = meta.getColumns(null, null, tableName, columnName)) {
            return rs.next();
        }
    }

    public boolean addMeeting(Meeting meeting) throws SQLException {
        String sql = "INSERT INTO meeting (MID, Mname, Name, Mstarttime, Mendtime, Place, Location, Room, Form, Type, CID, ID, Status) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, meeting.getMID());
            pstmt.setString(2, meeting.getMname());
            pstmt.setString(3, meeting.getName());
            pstmt.setTimestamp(4, new Timestamp(meeting.getMstarttime().getTime()));
            pstmt.setTimestamp(5, new Timestamp(meeting.getMendtime().getTime()));
            pstmt.setString(6, meeting.getPlace());
            pstmt.setString(7, meeting.getLocation());
            pstmt.setString(8, meeting.getRoom());
            pstmt.setString(9, meeting.getForm());
            pstmt.setString(10, meeting.getType());
            pstmt.setString(11, meeting.getCID());
            pstmt.setString(12, meeting.getID());
            pstmt.setString(13, meeting.getStatus());

            int result = pstmt.executeUpdate();
            return result > 0;
        }
    }

    public List<Meeting> getAllMeetings() {
        List<Meeting> meetings = new ArrayList<>();

        // 正确的SQL语句（注意列名和格式）
        String sql = "SELECT MID, Mname, Name, Mstarttime, Mendtime, Place, Location, Room, Form, Type, Status " +
                "FROM meeting ORDER BY Mstarttime DESC";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            System.out.println("✅ SQL查询执行成功");

            while (rs.next()) {
                Meeting meeting = new Meeting();
                meeting.setMID(rs.getString("MID"));
                meeting.setMname(rs.getString("Mname"));  // 注意是Mname不是Whame
                meeting.setName(rs.getString("Name"));
                meeting.setMstarttime(rs.getTimestamp("Mstarttime"));  // 注意是Mstarttime不是Nstarttime
                meeting.setMendtime(rs.getTimestamp("Mendtime"));
                meeting.setPlace(rs.getString("Place"));  // 注意是Place不是Pla
                meeting.setLocation(rs.getString("Location"));
                meeting.setRoom(rs.getString("Room"));
                meeting.setForm(rs.getString("Form"));
                meeting.setType(rs.getString("Type"));
                meeting.setStatus(rs.getString("Status"));

                meetings.add(meeting);
            }
        } catch (SQLException e) {
            System.err.println("❌ 数据库查询错误:");
            e.printStackTrace();
        }

        System.out.println("获取到 " + meetings.size() + " 条会议记录");
        return meetings;
    }
}
